<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
	<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=utf-8">
	<TITLE>Mapping related tables: one-to-many (tut2.cpp)</TITLE>
	<META NAME="GENERATOR" CONTENT="LibreOffice 3.5  (Linux)">
	<META NAME="AUTHOR" CONTENT="Viacheslav Naydenov">
	<META NAME="CREATED" CONTENT="20130520;9440700">
	<META NAME="CHANGEDBY" CONTENT="Viacheslav Naydenov">
	<META NAME="CHANGED" CONTENT="20140710;10514600">
	<STYLE TYPE="text/css">
	<!--
		@page { margin: 2cm }
		P { margin-top: 0.1cm; margin-bottom: 0.1cm }
		H2 { margin-bottom: 0.21cm }
		H2.western { font-family: "Times New Roman", serif }
		H2.cjk { font-family: "Droid Sans Fallback" }
		H2.ctl { font-family: "Lohit Hindi" }
		PRE.western { font-family: "Courier New", monospace }
		PRE.cjk { font-family: "Droid Sans Fallback", monospace }
		PRE.ctl { font-family: "Lohit Hindi", monospace }
		TT.western { font-family: "Courier New", monospace }
		TT.cjk { font-family: "Droid Sans Fallback", monospace }
		TT.ctl { font-family: "Lohit Hindi", monospace }
	-->
	</STYLE>
</HEAD>
<BODY LANG="en-US" DIR="LTR">
<H2 CLASS="western">Mapping related tables: one-to-many (tut2.cpp)</H2>
<P>Let's take a look at <EM>one-to-many</EM> kind of relationship,
consider the example Client – Orders. One client may have zero or
more orders in some accounting system, each order belongs to some
client. Clients are stored in table <TT CLASS="western">client_tbl</TT>,
while their orders are stored in table <TT CLASS="western">order_tbl</TT>.</P>
<P>At the SQL level this relationship can be expressed as having a
<EM>foreign key constraint</EM> on column <TT CLASS="western">client_id</TT>
in the <EM>child table</EM> <TT CLASS="western">order_tbl</TT>
referencing column <TT CLASS="western">id</TT> in the <EM>parent
table</EM> <TT CLASS="western">client_tbl</TT>.</P>
<P>At the ORM level such a relationship is represented by the
objects' properties. An instance of a class that maps to the child
table usually has an <EM>object-reference property</EM>, referencing
the parent object. From the other side of the relationship, an
instance of a class that maps to the parent table may have a
<EM>collection-of-objects property</EM> (which is sometimes called
&quot;backref&quot;), to be able to iterate all over its children.</P>
<P>First, let's define data schema with two classes <TT CLASS="western">Client</TT>
and <TT CLASS="western">Order</TT>, mapped to two tables <TT CLASS="western">client_tbl</TT>
and <TT CLASS="western">order_tbl</TT>. Also let's define the
relationship between the classes.</P>
<PRE CLASS="western">&lt;schema&gt;
    &lt;table name=&quot;client_tbl&quot; sequence=&quot;client_seq&quot; class=&quot;Client&quot; xml-name=&quot;client&quot;&gt;
        &lt;column name=&quot;id&quot; type=&quot;longint&quot;&gt;
            &lt;primary-key /&gt;
        &lt;/column&gt;
        &lt;column name=&quot;dt&quot; type=&quot;datetime&quot; null=&quot;false&quot; default=&quot;sysdate&quot; /&gt;
        &lt;column name=&quot;name&quot; type=&quot;string&quot; size=&quot;100&quot; null=&quot;false&quot; /&gt;
        &lt;column name=&quot;email&quot; type=&quot;string&quot; size=&quot;100&quot; null=&quot;false&quot; /&gt;
        &lt;column name=&quot;phone&quot; type=&quot;string&quot; size=&quot;50&quot; null=&quot;true&quot; /&gt;
        &lt;column name=&quot;budget&quot; type=&quot;decimal&quot; /&gt;
    &lt;/table&gt;
    &lt;table name=&quot;order_tbl&quot; sequence=&quot;order_seq&quot; class=&quot;Order&quot; xml-name=&quot;order&quot;&gt;
        &lt;column name=&quot;id&quot; type=&quot;longint&quot;&gt;
            &lt;primary-key /&gt;
        &lt;/column&gt;
        &lt;column name=&quot;client_id&quot; type=&quot;longint&quot; null=&quot;false&quot;&gt;
            &lt;foreign-key table=&quot;client_tbl&quot; key=&quot;id&quot;/&gt;
        &lt;/column&gt;
        &lt;column name=&quot;dt&quot; type=&quot;datetime&quot; null=&quot;false&quot; default=&quot;sysdate&quot; /&gt;
        &lt;column name=&quot;memo&quot; type=&quot;string&quot; size=&quot;100&quot; /&gt;
        &lt;column name=&quot;total_sum&quot; type=&quot;decimal&quot; null=&quot;false&quot; /&gt;
        &lt;column name=&quot;paid_sum&quot; type=&quot;decimal&quot; default=&quot;0&quot; /&gt;
        &lt;column name=&quot;paid_dt&quot; type=&quot;datetime&quot; /&gt;
    &lt;/table&gt;
    &lt;relation type=&quot;one-to-many&quot;&gt;
        &lt;one class=&quot;Client&quot; property=&quot;orders&quot; /&gt;
        &lt;many class=&quot;Order&quot; property=&quot;owner&quot; /&gt;
    &lt;/relation&gt;
&lt;/schema&gt;</PRE><P>
In the table definition of <TT CLASS="western">order_tbl</TT> the
column <TT CLASS="western">client_id</TT> is defined to be a foreign
key, it acts like a link between the tables. When there is only one
such link between two tables, then there is no need to specify
additional parameters in the element <TT CLASS="western">&lt;relation&gt;</TT>.</P>
<P>SQLite code for the definition above looks like this (see
<A HREF="Tutorial1.en.html">Tutorial1</A>, how to use <TT CLASS="western">yborm_gen</TT>
utility):</P>
<PRE CLASS="western">CREATE TABLE client_tbl (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(50),
    budget NUMERIC
);

CREATE TABLE order_tbl (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    memo VARCHAR(100),
    total_sum NUMERIC NOT NULL,
    paid_sum NUMERIC DEFAULT 0,
    paid_dt TIMESTAMP
    , FOREIGN KEY (client_id) REFERENCES client_tbl(id)
);</PRE><P>
The following program uses the domain classes, generated from the XML
description, see <A HREF="Tutorial1.en.html">Tutorial1</A>. The
program creates the child object first and assigns some values to its
fields, then it creates the parent object, and then it links the
child to the parent. After all it stores the objects to the session
and flushes them to the database committing transaction as well
(<TT CLASS="western">session.commit();</TT>). Note, that these
objects will be stored in proper sequence (first - parent, second -
child), also note that the foreign key value is assigned
automatically, as well as primary key values. 
</P>
<PRE CLASS="western">#include &lt;iostream&gt;
#include &quot;domain/Client.h&quot;
#include &quot;domain/Order.h&quot;
int main()
{
    std::auto_ptr&lt;Yb::SqlConnection&gt; conn(new Yb::SqlConnection(
            &quot;sqlite+sqlite://./tut2.sqlite&quot;));
    Yb::Engine engine(Yb::Engine::READ_WRITE, conn);
    Yb::Session session(Yb::init_schema(), &amp;engine);

    Domain::Order::Holder order;
    std::string amount;
    std::cout &lt;&lt; &quot;Enter order amount: \n&quot;;
    std::cin &gt;&gt; amount;
    order-&gt;total_sum = Yb::Decimal(amount);

    Domain::Client::Holder client;
    std::string name, email;
    std::cout &lt;&lt; &quot;Enter name, email: \n&quot;;
    std::cin &gt;&gt; name &gt;&gt; email;
    client-&gt;name = name;
    client-&gt;email = email;
    client-&gt;dt = Yb::now();

    std::cout &lt;&lt; &quot;Client's orders count: &quot; &lt;&lt; client-&gt;orders.size() &lt;&lt; &quot;\n&quot;;
    order-&gt;owner = client;
    std::cout &lt;&lt; &quot;Client's orders count: &quot; &lt;&lt; client-&gt;orders.size() &lt;&lt; &quot;\n&quot;;

    order-&gt;save(session);
    client-&gt;save(session);
    session.commit();
    std::cout &lt;&lt; order-&gt;xmlize(1)-&gt;serialize() &lt;&lt; std::endl;
    return 0;
}</PRE><P>
Here you can see that linking a child (<TT CLASS="western">Order</TT>
class) to a parent (<TT CLASS="western">Client</TT> class) looks like
an assignment (<TT CLASS="western">order-&gt;owner = client;</TT>).
Also, this example operates on the domain classes in a different way
than it was seen in <A HREF="Tutorial1.en.html">Tutorial1</A>. A kind
of smart pointers <TT CLASS="western">&lt;DomainClass&gt;::Holder</TT>
is used here to implement the nested or recurrent object references.
That is, all the object-properties are implemented using
<TT CLASS="western">&lt;DomainClass&gt;::Holder</TT> classes, and
therefore they must be dereferenced using an arrow (<TT CLASS="western">-&gt;</TT>),
and not a dot (<TT CLASS="western">.</TT>).</P>
<P>Let's compile the example:</P>
<PRE CLASS="western">$ c++ -I. -I$YBORM_ROOT/include/yb -o tut2 tut2.cpp domain/Client.cpp domain/Order.cpp -L$YBORM_ROOT/lib -lybutil -lyborm</PRE><P>
And then run it:</P>
<PRE CLASS="western">$ ./tut2
Enter order amount: 
45.67
Enter name, email: 
Petya pet@ya.ru
Client's orders count: 0
Client's orders count: 1
&lt;order&gt;&lt;id&gt;2&lt;/id&gt;&lt;owner&gt;&lt;id&gt;2&lt;/id&gt;&lt;dt&gt;2014-06-01T17:25:32&lt;/dt&gt;&lt;name&gt;Petya&lt;/name&gt;&lt;email&gt;pet@ya.ru&lt;/email&gt;&lt;budget is_null=&quot;1&quot;/&gt;&lt;/owner&gt;&lt;dt&gt;2014-06-01T17:25:17&lt;/dt&gt;&lt;memo is_null=&quot;1&quot;/&gt;&lt;total-sum&gt;45.67&lt;/total-sum&gt;&lt;paid-sum&gt;0&lt;/paid-sum&gt;&lt;paid-dt is_null=&quot;1&quot;/&gt;&lt;/order&gt;</PRE><P>
If we would trace the SQL operators, performing the same steps as in
<A HREF="Tutorial1.en.html">Tutorial1</A>, we could see the
following:</P>
<PRE CLASS="western">14-06-01 17:25:32.293 31927/31927 DEBG orm: flush started
14-06-01 17:25:32.293 31927/31927 DEBG sql: begin transaction
14-06-01 17:25:32.293 31927/31927 DEBG sql: prepare: INSERT INTO client_tbl (dt, name, email, budget) VALUES (?, ?, ?, ?)
14-06-01 17:25:32.294 31927/31927 DEBG sql: bind: (DateTime, String, String, Decimal)
14-06-01 17:25:32.294 31927/31927 DEBG sql: exec prepared: p1=&quot;'2014-06-01 17:25:32'&quot; p2=&quot;'Petya'&quot; p3=&quot;'pet@ya.ru'&quot; p4=&quot;NULL&quot;
14-06-01 17:25:32.295 31927/31927 DEBG sql: prepare: SELECT SEQ LID FROM SQLITE_SEQUENCE WHERE NAME = 'client_tbl'
14-06-01 17:25:32.295 31927/31927 DEBG sql: exec prepared:
14-06-01 17:25:32.295 31927/31927 DEBG sql: fetch: LID='2' 
14-06-01 17:25:32.295 31927/31927 DEBG sql: fetch: no more rows
14-06-01 17:25:32.295 31927/31927 DEBG sql: prepare: INSERT INTO order_tbl (client_id, dt, memo, total_sum, paid_sum, paid_dt) VALUES (?, ?, ?, ?, ?, ?)
14-06-01 17:25:32.295 31927/31927 DEBG sql: bind: (LongInt, DateTime, String, Decimal, Decimal, DateTime)
14-06-01 17:25:32.295 31927/31927 DEBG sql: exec prepared: p1=&quot;2&quot; p2=&quot;'2014-06-01 17:25:17'&quot; p3=&quot;NULL&quot; p4=&quot;45.67&quot; p5=&quot;0&quot; p6=&quot;NULL&quot;
14-06-01 17:25:32.295 31927/31927 DEBG sql: prepare: SELECT SEQ LID FROM SQLITE_SEQUENCE WHERE NAME = 'order_tbl'
14-06-01 17:25:32.296 31927/31927 DEBG sql: exec prepared:
14-06-01 17:25:32.296 31927/31927 DEBG sql: fetch: LID='2' 
14-06-01 17:25:32.296 31927/31927 DEBG sql: fetch: no more rows
14-06-01 17:25:32.296 31927/31927 DEBG orm: flush finished OK
14-06-01 17:25:32.296 31927/31927 DEBG sql: commit
14-06-01 17:25:32.395 31927/31927 DEBG sql: prepare: SELECT order_tbl.id, order_tbl.client_id, order_tbl.dt, order_tbl.memo, order_tbl.total_sum, order_tbl.paid_sum, order_tbl.paid_dt FROM order_tbl WHERE order_tbl.id = ?
14-06-01 17:25:32.395 31927/31927 DEBG sql: exec prepared: p1=&quot;2&quot;
14-06-01 17:25:32.395 31927/31927 DEBG sql: fetch: ID='2' CLIENT_ID='2' DT='2014-06-01T17:25:17' MEMO=NULL TOTAL_SUM='45.67' RECEIPT_SUM='0' RECEIPT_DT=NULL 
14-06-01 17:25:32.395 31927/31927 DEBG sql: fetch: no more rows
14-06-01 17:25:32.395 31927/31927 DEBG sql: prepare: SELECT client_tbl.id, client_tbl.dt, client_tbl.name, client_tbl.email, client_tbl.budget FROM client_tbl WHERE client_tbl.id = ?
14-06-01 17:25:32.396 31927/31927 DEBG sql: exec prepared: p1=&quot;2&quot;
14-06-01 17:25:32.396 31927/31927 DEBG sql: fetch: ID='2' DT='2014-06-01T17:25:32' NAME='Petya' EMAIL='pet@ya.ru' BUDGET=NULL 
14-06-01 17:25:32.396 31927/31927 DEBG sql: fetch: no more rows</PRE><P>
Note the correct sequence of insertion (first – parent, second –
child). This is achieved by doing the topological sort on the graph
of objects. The value of foreign key is assigned automatically, as
well as the values of primary keys.</P>
<P>On linking these objects using an assignment we can see that the
<TT CLASS="western">orders</TT> collection of the corresponding
instance of <TT CLASS="western">Client</TT> class gets altered as
well. This is because both <TT CLASS="western">client-&gt;orders</TT>
and <TT CLASS="western">order-&gt;owner</TT> properties share the
same internal object <TT CLASS="western">RelationObject</TT>. The
same effect could be achieved if we would insert into the
collection-property. Let's change the assignment at the child object
side</P>
<PRE CLASS="western">    order-&gt;owner = client;</PRE><P>
to the insertion into the collection-property at the parent object
side</P>
<PRE CLASS="western">    client-&gt;orders.insert(*order);</PRE><P>
These two lines do exactly the same thing.</P>
</BODY>
</HTML>
